package com.dp.service.impl3;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.JsonPage;
import com.dp.pojo.OccupancyVo;
import com.dp.util.DataMap;
import com.dp.vo.TOccupancy;

@Service
@Transactional
public class CheckinqueryServiceImpl extends BaseImpl {
	
	/**
	 * Find all the OccupancyVo Entity
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<OccupancyVo>
	 */
	public List<OccupancyVo> findAllOccupancyEntity(DataMap hashMap, Integer firstrow,
			Integer maxrow){
//		JsonPage jpage=(JsonPage) hashMap.get("jpage");
//		OccupancyVo occupancy=(OccupancyVo) hashMap.get("occupancy");
//		String spcodeS=(String) hashMap.get("spcodeS");
//		String begintime = (String)hashMap.get("begintime");
//		String endtime = (String)hashMap.get("endtime");
//		String sql="SELECT tc.cnumber,tc.ctype,ts.spname,t.roomnumber,t.checkinnumber,t.checkin_time,t.plancheckin_time,t.actualcheckin_time,t.checkout,t.id FROM t_occupancy t,t_certificate tc,t_spcode ts WHERE t.cid=tc.id AND t.spcode=ts.spcode ";
//		if(spcodeS!=null){
//			sql += " AND ts.spcode in ("+spcodeS+")";
//		}
//		if(occupancy!=null){
//			if(occupancy.getCnumber()!=null&& occupancy.getCnumber().toString().trim().length() > 0){
//				sql += " AND tc.cnumber ='"
//						+ occupancy.getCnumber().toString().trim() + "'";
//			}
//			if(occupancy.getCtype()!=null&& occupancy.getCtype().toString().trim().length() > 0){
//				if(occupancy.getCtype()==1){
//					sql += " AND ( tc.ctype=1 or tc.ctype=2)";
//				}else{
//					sql += " AND tc.ctype ='"
//						+ occupancy.getCtype().toString().trim() + "'";
//				}
//			}
//			if (occupancy.getSpname() != null && occupancy.getSpname().toString().trim().length() > 0) {
//				sql += " AND ts.spname like '%"
//						+ occupancy.getSpname().toString().trim() + "%'";
//			}
//		}
//		
//		if(null != begintime && !begintime.equals("")){
//			sql += " and t.checkin_time >= '" + begintime + "'";
//		}
//		if(null != endtime && !endtime.equals("")){
//			sql += " and t.checkin_time <= '" + endtime + "'";
//		}
//		if (jpage != null && jpage.getSidx() != null
//				&& !"".equals(jpage.getSidx())) {
//			sql += " order by " + jpage.getSidx();
//			sql += " " + jpage.getSord();
//		}
//		sql += " limit " +firstrow+","+maxrow;
//		List<OccupancyVo> result=new ArrayList<OccupancyVo>();
//		SimpleDateFormat fmt_temp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//		try {
//			this.initRptConnection();
//			log.info(" findAllOccupancyEntity excute sql: " +sql);
//			this.rs1 = this.stmt.executeQuery(sql);
//			while(rs1.next()){
//				OccupancyVo one = new OccupancyVo();
//				one.setCnumber(rs1.getString(1));
//				one.setCtype(rs1.getInt(2));
//				one.setSpname(rs1.getString(3));
//				one.setRoomnumber(rs1.getString(4));
//				one.setCheckinnumber(rs1.getInt(5));
//				one.setCheckin_time(fmt_temp.parse(rs1.getString(6)));
//				if(rs1.getString(7)!=null){
//					one.setPlancheckin_time(fmt_temp.parse(rs1.getString(7)));
//				}
//				if(rs1.getString(8)!=null){
//					one.setActualcheckin_time(fmt_temp.parse(rs1.getString(8)));
//				}
//				one.setCheckout(rs1.getInt(9));
//				one.setId(rs1.getInt(10));
//				result.add(one);
//			}
//		} catch (Exception e) {
//			e.printStackTrace();
//		} finally{
//			this.closeAll();
//		}
		return null;//result;
	}
	
	/**
	 * 入住信息 记录数
	 * @param hashMap
	 * @return Integer
	 */
	public Integer findAllOccupancyEntityCount(DataMap hashMap){
		OccupancyVo occupancy=(OccupancyVo) hashMap.get("occupancy");
		String spcodeS=(String) hashMap.get("spcodeS");
		String begintime = (String)hashMap.get("begintime");
		String endtime = (String)hashMap.get("endtime");
		String sql="SELECT count(t.id) FROM t_occupancy t,t_certificate tc,t_spcode ts WHERE t.cid=tc.id AND t.spcode=ts.spcode ";
		if(spcodeS!=null){
			sql += " AND ts.spcode in ("+spcodeS+")";
		}
		if(occupancy!=null){
			if(occupancy.getCnumber()!=null&& occupancy.getCnumber().toString().trim().length() > 0){
				sql += " AND tc.cnumber ='"
						+ occupancy.getCnumber().toString().trim() + "'";
			}
			if(occupancy.getCtype()!=null&& occupancy.getCtype().toString().trim().length() > 0){
				if(occupancy.getCtype()==1){
					sql += " AND ( tc.ctype=1 or tc.ctype=2)";
				}else{
					sql += " AND tc.ctype ='"
						+ occupancy.getCtype().toString().trim() + "'";
				}
			}
			if (occupancy.getSpname() != null && occupancy.getSpname().toString().trim().length() > 0) {
				sql += " AND ts.spname like '%"
						+ occupancy.getSpname().toString().trim() + "%'";
			}
		}
		if(null != begintime && !begintime.equals("")){
			sql += " and t.checkin_time >= '" + begintime + "'";
		}
		if(null != endtime && !endtime.equals("")){
			sql += " and t.checkin_time <= '" + endtime + "'";
		}
		int count=0;
//		try {
//			this.initRptConnection();
//			log.info(" findAllOccupancyEntityCount excute sql: " +sql);
//			this.rs1 = this.stmt.executeQuery(sql);
//			if(rs1.next()){
//				count=rs1.getInt(1);
//			}
//		} catch (Exception e) {
//			e.printStackTrace();
//		} finally{
//			this.closeAll();
//		}
		return count;
	}

	public OccupancyVo findOccupancyById(Integer id) {
		String sql="SELECT t.id,tc.cnumber,tc.ctype,tc.cname,tc.csex,tc.nation,tc.birth_date,tc.cadress,tc.cphoto,tc.issuing_authority,tc.valid_period,tc.note,ts.spname,t.sn,t.roomnumber,t.checkinnumber,t.checkin_time,t.plancheckin_time,t.actualcheckin_time,t.checkout FROM t_occupancy t,t_certificate tc,t_spcode ts WHERE t.cid=tc.id AND t.spcode=ts.spcode AND t.id='"+id+"'";
		OccupancyVo one = null;
		SimpleDateFormat fmt_temp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
//			this.initRptConnection();
//			log.info(" findOccupancyById excute sql: " +sql);
//			this.rs1 = this.stmt.executeQuery(sql);
//			if(rs1.next()){
//				one = new OccupancyVo();
//				one.setId(rs1.getInt(1));
//				one.setCnumber(rs1.getString("tc.cnumber"));
//				one.setCtype(rs1.getInt("tc.ctype"));
//				one.setCname(rs1.getString("tc.cname"));
//				one.setCsex(rs1.getString("tc.csex"));
//				one.setNation(rs1.getString("tc.nation"));
//				one.setBirth_date(rs1.getString("tc.birth_date"));
//				one.setCadress(rs1.getString("tc.cadress"));
//				if(rs1.getBlob("tc.cphoto")!=null&&rs1.getBlob("tc.cphoto").length()!=0){
//					one.setCphoto(rs1.getBlob("tc.cphoto"));
//				}
//				one.setIssuing_authority(rs1.getString("tc.issuing_authority"));
//				one.setValid_period(rs1.getString("tc.valid_period"));
//				one.setNote(rs1.getString("tc.note"));
//				
//				one.setSpname(rs1.getString("ts.spname"));
//				one.setSn(rs1.getString("t.sn"));
//				one.setRoomnumber(rs1.getString("t.roomnumber"));
//				one.setCheckinnumber(rs1.getInt("t.checkinnumber"));
//				one.setCheckin_time(fmt_temp.parse(rs1.getString("t.checkin_time")));
//				if(rs1.getString("t.plancheckin_time")!=null){
//					one.setPlancheckin_time(fmt_temp.parse(rs1.getString("t.plancheckin_time")));
//				}
//				if(rs1.getString("t.actualcheckin_time")!=null){
//					one.setActualcheckin_time(fmt_temp.parse(rs1.getString("t.actualcheckin_time")));
//				}
//				one.setCheckout(rs1.getInt("t.checkout"));
//			}
		} catch (Exception e) {
			e.printStackTrace();
//			if(conn!=null){
//				try {
//					conn.rollback();
//				} catch (SQLException e1) {
//					e1.printStackTrace();
//				} 
//			}
//		} finally{
//			this.closeAll();
		}
		return one;
	}
	
	public OccupancyVo getImageById(Integer id) {
		String sql="SELECT tc.cphoto FROM t_occupancy t,t_certificate tc WHERE t.cid=tc.id AND t.id='"+id+"'";
		OccupancyVo one = null;
		try {
//			this.initRptConnection();
//			log.info(" getImageById excute sql: " +sql);
//			this.rs1 = this.stmt.executeQuery(sql);
//			if(rs1.next()){
//				one = new OccupancyVo();
//				one.setCphoto(rs1.getBlob(1));
//			}
		} catch (Exception e) {
			e.printStackTrace();
		
		}
		return one;
	}

}
